<?php

/**
 * This is the model class for table "books".
 *
 * The followings are the available columns in table 'books':
 * @property integer $book_id
 * @property string $book_name
 * @property string $book_image
 * @property integer $book_date_create
 * @property integer $book_date_modify
 * @property integer $book_date_publish
 * @property integer $book_cat_id
 * @property integer $book_pub_id
 * @property string $book_num_page
 * @property integer $book_price
 * @property integer $book_sale
 * @property string $book_content
 * @property integer $book_active
 * @property integer $book_special
 */
class Books extends CActiveRecord
{
	/**
	 * @return string the associated database table name
	 */
	public function tableName()
	{
		return 'books';
	}

	/**
	 * @return array validation rules for model attributes.
	 */
	public function rules()
	{
		// NOTE: you should only define rules for those attributes that
		// will receive user inputs.
		return array(
			array('book_name, book_image, book_price, book_num_page,', 'required'),
			array('book_date_create, book_date_modify, book_date_publish, book_cat_id, book_pub_id, book_price, book_sale, book_active, book_special', 'numerical', 'integerOnly'=>true),
			array('book_name, book_image, book_num_page', 'length', 'max'=>255),
         array('book_sale','numerical','max' => 99,'message' => 'SALE không được vượt quá 99%'),
			array('book_content', 'safe'),
			// The following rule is used by search().
			// @todo Please remove those attributes that should not be searched.
			array('book_id, book_name, book_image, book_date_create, book_date_modify, book_date_publish, book_cat_id, book_pub_id, book_num_page, book_price, book_sale, book_content, book_active, book_special', 'safe', 'on'=>'search'),
		);
	}

	/**
	 * @return array relational rules.
	 */
	public function relations()
	{
		// NOTE: you may need to adjust the relation name and the related
		// class name for the relations automatically generated below.
		return array(
         'category' => array(self::BELONGS_TO,'Category','book_cat_id'),
         'publisher' => array(self::BELONGS_TO,'Publishers','book_pub_id'),
         'authors' => array(self::HAS_MANY,'BookAuthor','book_id'),
		);
	}

	/**
	 * @return array customized attribute labels (name=>label)
	 */
	public function attributeLabels()
	{
		return array(
			'book_id' => 'Book',
			'book_name' => 'Tên sách',
			'book_image' => 'Ảnh',
			'book_date_create' => 'Ngày tạo',
			'book_date_modify' => 'Ngày cập nhật',
			'book_date_publish' => 'Ngày phát hành',
			'book_cat_id' => 'Danh mục',
			'book_pub_id' => 'NXB',
			'book_num_page' => 'Số trang',
			'book_price' => 'Giá',
			'book_sale' => 'Sale',
			'book_content' => 'Nội dung',
			'book_active' => 'Hiển thị',
			'book_special' => 'SP nổi bật',
		);
	}

	/**
	 * Retrieves a list of models based on the current search/filter conditions.
	 *
	 * Typical usecase:
	 * - Initialize the model fields with values from filter form.
	 * - Execute this method to get CActiveDataProvider instance which will filter
	 * models according to data in model fields.
	 * - Pass data provider to CGridView, CListView or any similar widget.
	 *
	 * @return CActiveDataProvider the data provider that can return the models
	 * based on the search/filter conditions.
	 */
	public function search()
	{
		// @todo Please modify the following code to remove attributes that should not be searched.

		$criteria=new CDbCriteria;

		$criteria->compare('book_id',$this->book_id);
		$criteria->compare('book_name',$this->book_name,true);
		$criteria->compare('book_image',$this->book_image,true);
		$criteria->compare('book_date_create',$this->book_date_create);
		$criteria->compare('book_date_modify',$this->book_date_modify);
		$criteria->compare('book_date_publish',$this->book_date_publish);
		$criteria->compare('book_cat_id',$this->book_cat_id);
		$criteria->compare('book_pub_id',$this->book_pub_id);
		$criteria->compare('book_num_page',$this->book_num_page,true);
		$criteria->compare('book_price',$this->book_price);
		$criteria->compare('book_sale',$this->book_sale);
		$criteria->compare('book_content',$this->book_content,true);
		$criteria->compare('book_active',$this->book_active);
		$criteria->compare('book_special',$this->book_special);

		return new CActiveDataProvider($this, array(
			'criteria'=>$criteria,
		));
	}

	/**
	 * Returns the static model of the specified AR class.
	 * Please note that you should have this exact method in all your CActiveRecord descendants!
	 * @param string $className active record class name.
	 * @return Books the static model class
	 */
	public static function model($className=__CLASS__)
	{
		return parent::model($className);
	}
   public function linkDetail($id,$title){
      return '/sach/' . CVietnameseTools::makeUrlFriendly($title).'-'.$id.'.html';
   }
   
   /**
    * Get list review
   */
   public function getReview($id){
      $reviews = array();
      if($id > 0){
         $sql = "SELECT
                  	reviews.rev_id,
                  	reviews.rev_product_id,
                  	reviews.rev_use_id,
                  	reviews.rev_rating,
                  	reviews.rev_date,
                  	reviews.rev_content
                  FROM
                  	reviews
                  WHERE
                     reviews.rev_product_id =:id";
         $db = Yii::app()->db;
         $cmd = $db->createCommand($sql);
         $cmd->bindParam("id",$id);
         $reviews = $cmd->queryAll();
      }
      return $reviews;
   }
   /**
    * Search like keyword
   */
   public function searchBooks($keyword){
      $array = array();
      if($keyword != ""){
         $sql = "SELECT
                  books.book_id,
                  books.book_name,
                  books.book_image,
                  books.book_num_page,
                  books.book_price,
                  book_author.book_id,
                  authors.aut_name
               FROM
                  books
               INNER JOIN book_author ON books.book_id = book_author.book_id
               INNER JOIN `authors` ON book_author.aut_id = `authors`.aut_id
               WHERE 
                  aut_name LIKE '%".$keyword."%' OR book_name LIKE '%".$keyword."%'
               GROUP BY 
                  books.book_id
               ORDER BY 
                  book_date_modify DESC" ;
         $db = Yii::app()->db;
         $cmd = $db->createCommand($sql);
         $cmd->bindParam("id",$id);
         $array = $cmd->queryAll();
      }
      return $array;
   }
   
   /**
    * Return list books feature 
    * @param array fields
    * @param string limit
   */
   public function getAllBooksFeature($fields = array(),$limit = ''){
      $limit = ($limit != '') ? $limit = ' LIMIT ' . $limit : '';
      if(count($fields) == 0){
         $sql = "SELECT * ";
      }else{
         $sql = " SELECT ";
         foreach($fields as $field){
            $sql .= $field . ',';
         }
         $sql = substr($sql,0,-1);
        
      }
      $sql .= " FROM " . $this->tableName() . "
                  INNER JOIN
                     publishers
                  ON
                     books.book_pub_id = publishers.pub_id 
                  WHERE  
                     book_active = 1 
                  AND 
                     book_feature = 1
                  ORDER BY 
                     book_date_modify DESC
                  " . $limit; 
      $db = Yii::app()->db;
      $cmd = $db->createCommand($sql);
      $returns = $cmd->queryAll();
      return $returns;
   }
   
   /**
    * 
    * Return list books newest
    * @param array fields
    * @param string limit
   */
   public function getAllBooksNew($fields = array(),$limit = ''){
      $limit = ($limit != '') ? $limit = ' LIMIT ' . $limit : '';
      if(count($fields) == 0){
         $sql = "SELECT * ";
      }else{
         $sql = " SELECT ";
         foreach($fields as $field){
            $sql .= $field . ',';
         }
         $sql = substr($sql,0,-1);
        
      }
      $sql .= " FROM " . $this->tableName() . "
                  INNER JOIN
                     publishers
                  ON
                     books.book_pub_id = publishers.pub_id
                  WHERE  
                     book_active = 1 
                  ORDER BY 
                     book_date_modify DESC
                  " . $limit; 
      $db = Yii::app()->db;
      $cmd = $db->createCommand($sql);
      $returns = $cmd->queryAll();
      return $returns;
   }
   
   /**
    * Return list book by category id
    * @param int $cat_id
    * @param array $fields
    * @param string $limit
    * @return array books
   */
   public function getAllBooksByCate($cat_id,$fields = array (),$limit = ''){
      $limit = ($limit != '') ? $limit = ' LIMIT ' . $limit : '';
      if(count($fields) == 0){
         $sql = "SELECT * ";
      }else{
         $sql = " SELECT ";
         foreach($fields as $field){
            $sql .= $field . ',';
         }
         $sql = substr($sql,0,-1);
      }
      $sql .= " FROM " . $this->tableName() . "
                  INNER JOIN
                     publishers
                  ON
                     books.book_pub_id = publishers.pub_id
                  WHERE  
                     book_active = 1
                  AND
                     book_cat_id =:cat_id 
                  ORDER BY 
                     book_date_modify DESC
                  " . $limit; 
      $db = Yii::app()->db;
      $cmd = $db->createCommand($sql);
      $cmd->bindParam(":cat_id",$cat_id);
      $returns = $cmd->queryAll();
      return $returns;
   }
   
   /**
    * 
    * Get info book by id
    * @param int $id
    * @param array fields
    * @return array info
   */
   public function getInfo($id,$fields = array()){
      
      $info = array();
      if($id > 0){
         if(count($fields) == 0){
            $sql = "SELECT * ";
         }else{
            $sql = " SELECT ";
            foreach($fields as $field){
               $sql .= $field . ',';
            }
            $sql = substr($sql,0,-1);
         }
         $sql .= " FROM " . $this->tableName() . " WHERE book_id =:id";
         $db = Yii::app()->db;
         $cmd = $db->createCommand($sql);
         $cmd->bindParam("id",$id);
         $info = $cmd->queryRow();
      }
      return $info;
   }
   
   /**
    * Get publishers
    * @param int $id
    * @return array info publishers
   */
   public function getPublishers($id){
      $publisher = array();
      if($id > 0){
         $sql = "SELECT
                  	publishers.pub_id,
                  	publishers.pub_name
                  FROM
                  	books
                  INNER JOIN
                  	publishers
                  ON
                     publishers.pub_id = books.book_pub_id
                  WHERE
                     books.book_id =:id";
         $db = Yii::app()->db;
         $cmd = $db->createCommand($sql);
         $cmd->bindParam("id",$id);
         $publisher = $cmd->queryRow();
      }
      return $publisher;
      
   }
   
   /**
    * Get category
    * @param int $id
    * @return array info category
   */
   public function getCategory($id){
      $category = array();
      if($id > 0){
         $sql = "SELECT
                  	category.cat_id,
                  	category.cat_name
                  FROM
                  	books
                  INNER JOIN 
                     category 
                  ON 
                     category.cat_id = books.book_cat_id
                  WHERE
                     books.book_id =:id";
         $db = Yii::app()->db;
         $cmd = $db->createCommand($sql);
         $cmd->bindParam("id",$id);
         $category = $cmd->queryRow();
      }
      return $category;
      
   }
   
   /**
    * Get author
    * @param int $id
    * @return array author
   */
   public function getAuthor($id){
      $authors = array();
      if($id > 0){
         $sql = "SELECT
                  	authors.aut_name,
                  	authors.aut_id
                  FROM
                  	book_author
                  INNER JOIN authors ON book_author.aut_id = authors.aut_id
                  WHERE
                     book_id =:id";
         $db = Yii::app()->db;
         $cmd = $db->createCommand($sql);
         $cmd->bindParam("id",$id);
         $authors = $cmd->queryAll();
      }
      return $authors;
      
   }
}
